package ua.com.fesvlast.hotel.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ua.com.fesvlast.hotel.model.connectionpool.ConnectionManager;
import ua.com.fesvlast.hotel.model.enteties.Users;

public class UsersDAO {
	
	private static Connection conn=ConnectionManager.getConnection();
	
	public  List<Users> getListUsers(){
		List<Users> usersList =new ArrayList<Users>();
		String sql="SELECT userID, login, password, userType FROM users";
		Statement stmt=null;
		ResultSet rs=null;
			try{
				stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				rs=stmt.executeQuery(sql);
				while(rs.next()){
					Users user =new Users();
					user.setUserId(rs.getInt("userId"));
					user.setLogin(rs.getString("login"));
					user.setPassword(rs.getString("password"));
					user.setUserType(rs.getString("userType"));
					usersList.add(user);
				}
				
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			ConnectionManager.releaseConnection(conn);
		}
			return usersList;
	}
	
	public  boolean checkLoginUser(String login, String password) {
		
		String sql="SELECT * FROM users WHERE login = ? AND password = ?";
		boolean flag=false;
		ResultSet rs=null;
		PreparedStatement stmt=null;
		
		try{
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, login);
			stmt.setString(2, password);
			rs=stmt.executeQuery();
			
			if(rs.next())
				flag= true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
			if(rs !=null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
			}
			if(stmt !=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			ConnectionManager.releaseConnection(conn);
		}
		return flag;
	}
	
	public  Users createUser(Users user){
		String sql="INSERT into users (login, password, userType) VALUES (?,?, ?)";
		ResultSet keys=null;
		PreparedStatement stmt =null;
		
		try{
			stmt=conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS );
			stmt.setString(1, user.getLogin());
			stmt.setString(2, user.getPassword());
			stmt.setString(3, user.getUserType());
			int affected=stmt.executeUpdate();
			
			if(affected==1){
				keys =stmt.getGeneratedKeys();
				keys.next();
				int newKey=keys.getInt(1);
				user.setUserId(newKey);
			}
		}catch (SQLException e) {
			System.err.println(e);
		}finally{
			if(keys !=null){
				try {
					keys.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}	
		}
		ConnectionManager.releaseConnection(conn);
		return user;
	}
	
	public Users getUser(String login, String password){
		Users user=null;
		String sql="SELECT * FROM users WHERE login = ? AND password = ?";
		ResultSet rs=null;
		PreparedStatement stmt=null;
		
		try{
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, login);
			stmt.setString(2, password);
			rs=stmt.executeQuery();
			
			if(rs.next()){
				user=new Users();
				user.setLogin(rs.getString("login"));
				user.setPassword(rs.getString("password"));
				user.setUserId(rs.getInt("userId"));
				user.setUserType(rs.getString("userType"));
			}
		}catch(SQLException e){
			System.err.println(e);
		}finally{
			
			if(rs !=null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
			}
			if(stmt !=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			ConnectionManager.releaseConnection(conn);
		}
		return user;
	}
}
